package utils;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


/**
 * 解析Excel的工具类
 */

public class ExcelReader {

    public static boolean isExcel(String fileName) {
        int dotIndex = fileName.lastIndexOf(".");
        if (dotIndex < 0) {
            return false;
        }
        String fileSuffix = fileName.substring(dotIndex);
        if (!".xls.xlsx.csv.".contains(fileSuffix)) {
            return false;
        }
        return true;
    }


    public static List<String[]> readFile(InputStream is, Integer ignoreLines, Integer sheetNum) throws FileNotFoundException {

        List<String[]> datas = new ArrayList<String[]>();
        try {
            Workbook workbook = null;
            try {
                workbook = WorkbookFactory.create(is);
            } catch (Exception e) {
                throw  new RuntimeException("文件格式错误");
            }
            if (sheetNum == null) {
                sheetNum = 0;
            }
            Sheet sheet = workbook.getSheetAt(sheetNum);
            int beginIndex = ignoreLines;
            for (int r = beginIndex; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                short cellNumbers = row.getLastCellNum();
                String[] rowData = new String[row.getLastCellNum()];
                FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

                for (int c = 0; c < cellNumbers; c++) {
                    Cell cell = row.getCell(c);
                    if (cell == null) {
                        rowData[c] = "";
                    } else {
                        if (cell.getCellComment() == null) {
                            rowData[c] = getStringCellValue(cell, formulaEvaluator);
                        } else {
                            rowData[c] = cell.getCellComment().getString().getString();
                        }
                    }
                }
                datas.add(rowData);
            }

            return datas;
        } finally {
            // close resource quietly
            try {
                is.close();
            } catch (IOException e) {
            }
        }
    }


   /**
     * @param path 输入流,此输入流不管解析成功还是失败都会被释放
     * @return
     * @throws FileNotFoundException
     */

    public static List<String[]> readFile(String path, int ignoreLines, Integer sheetNum) throws FileNotFoundException {

        File file = new File(path);
        InputStream is = new FileInputStream(file);
        return readFile(is, ignoreLines, sheetNum);
    }


    private static String getStringCellValue(Cell cell, FormulaEvaluator formulaEvaluator) {
        String value = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
                    value = cell.getDateCellValue().getTime() + "";
                } else {
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    value = cell.getStringCellValue();//trimZeroNumberValue(new BigDecimal(cell.getNumericCellValue() + "").toString());
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                value = formulaEvaluator.evaluate(cell).formatAsString();
                break;
            case Cell.CELL_TYPE_BLANK: // 空白
                value = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR: // Error，返回错误码
                value = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                value = "";
                break;
        }
        return value;
    }

    private static String trimZeroNumberValue(String numStr) {
        if (numStr == null) {
            return numStr;
        }
        int index = numStr.lastIndexOf(".");
        if (index >= 0) {
            String zs = numStr.substring(0, index);
            String xs = numStr.substring(index + 1);
            String zn = "";
            for (int i = 0; i < xs.length(); i++) {
                zn += "0";
            }
            if (zn.equals(xs)) {
                return zs;
            }
        }
        return numStr;
    }

    public static class OrderExport {
        private String dealCode;
        private String expressCompanyId;
        private String expressCompanyName;
        private String expressId;
        private String errorMsg;

        public String getDealCode() {
            return dealCode;
        }

        public void setDealCode(String dealCode) {
            this.dealCode = dealCode;
        }

        public String getExpressCompanyId() {
            return expressCompanyId;
        }

        public void setExpressCompanyId(String expressCompanyId) {
            this.expressCompanyId = expressCompanyId;
        }

        public String getExpressCompanyName() {
            return expressCompanyName;
        }

        public void setExpressCompanyName(String expressCompanyName) {
            this.expressCompanyName = expressCompanyName;
        }

        public String getExpressId() {
            return expressId;
        }

        public void setExpressId(String expressId) {
            this.expressId = expressId;
        }

        public String getErrorMsg() {
            return errorMsg;
        }

        public void setErrorMsg(String errorMsg) {
            this.errorMsg = errorMsg;
        }
    }

    public static void main(String[] args) throws FileNotFoundException {

        long start = System.currentTimeMillis();
        File file = new File("C:\\Users\\Administrator\\Desktop\\1.xlsx");
        InputStream is = new FileInputStream(file);
        List list = readFile(is, 0, null);
        long end = System.currentTimeMillis();
        System.out.println("读取2万条数据耗时"+(end-start));

    }
}

